package utils;

import java.sql.*;

/**
 * @author 陈金平
 * @Description
 * @create 2020/11/9 20:05
 */
public class JDBCUtil {
    public static final String DRIVER = "com.mysql.cj.jdbc.Driver";
    public static final String URL = "jdbc:mysql://localhost:3306/cart?characterEncoding=utf8&useSSL=true&serverTimezone=Asia/Shanghai";
    public static final String USERNAME = "root";
    public static final String PASSWORD = "123456";

    private Connection connection = null;
    private PreparedStatement statement = null;
    private ResultSet resultSet = null;

    public JDBCUtil() {
        getConnection();
    }

    public Connection getConnection() {
        try {
            //加载mysql驱动
            Class.forName(DRIVER);
            //链接数据库
            connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return connection;
    }

    public ResultSet executeQuery(String sql,Object...params){
        try {
            //新建一个SQL语句执行对象
            statement = connection.prepareStatement(sql);

            for (int i = 0; i < params.length; i++) {
                statement.setObject(i+1,params[i]);
            }

            resultSet = statement.executeQuery();
            return resultSet;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
            return null;
        }
    }
    public Integer executeUpdate(String sql,Boolean returnId,Object...params){
        try {
            if (returnId) {
                //执行sql语句并返回自增主键
                statement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            }
            else {
                statement = connection.prepareStatement(sql);
            }
            for (int i = 0; i < params.length; i++) {
                statement.setObject(i+1, params[i]);
            }

            Integer status = statement.executeUpdate();

            //不返回自增主键
            if (!returnId) {
                return status;
            }

            //需要返回主键
            ResultSet rSet = statement.getGeneratedKeys();
            if (rSet.next()) {
                return rSet.getInt(1);
                //取第一列的值
            }
            else {
                return null;
            }
        } catch (SQLException e) {
            e.printStackTrace();

            return 0;
        }
    }
    public int[] executeBatch(String sql,Object[][] params){
        try {
            statement = connection.prepareStatement(sql);

            //循环所有的输入参数
            for (int i = 0; i < params.length; i++) {
                //循环每一个参数，每一个参数为一个数组
                for (int j = 0; j < params[i].length; j++) {
                    statement.setObject(j+1,params[i][j]);
                }
                //加入批量处理
                statement.addBatch();
            }
            //执行批量处理
            return statement.executeBatch();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
            return null;
        }
    }
    public void close(){
        try {
            if (resultSet!=null){
                resultSet.close();
            }
            if (statement!=null){
                statement.close();
            }
            if (connection!=null){
                connection.close();
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }
}
